# encoding: utf-8
# A18 车型导入MT码，生成插入 sql
# author yeyi
# date 2021-01-07

import openpyxl
import traceback
import json
import time

# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook('.\\A18_MT_20201221.xlsx')

# 获取workbook中所有的表格
sheets = wb.sheetnames
offset = 1
# excel 中第一行数据的行数
lineStart = 2
# 车系ID
vehicle_series_id = '6'
# 数据库中 id 起始值: 两位车系id+三位自增（000开始自增）
vehicleModelIdStart = 6000
# 年份
year = '2021'
# 类型(0:小轿车，1:suv,2:MPV)
type = '0'
# 车型配置（低配，高配）
model_config = '1'
tsp_type = 'TSP2.0'
img = 'https://iov-app-carmodel-1254959430.cos.ap-guangzhou.myqcloud.com/a12_carmodel.png'
img1 = 'https://iov-app-carmodel-1254959430.cos.ap-guangzhou.myqcloud.com/a12_index_carmodel.png'
# None 不保存
img2 = None
gap = '_'

def printt(s):
    #print(s)
    f = open('importMT.log', 'a+', encoding='utf-8')
    print(s,file=f)
    f.close()

def fun(ws,i):
  seriesName = str(ws.cell(row=i, column=2).value)
  vehicle_model_code = str(ws.cell(row=i, column=6).value)
  saleCode = str(ws.cell(row=i, column=4).value)
  battery = str(ws.cell(row=i, column=3).value)
  vehicle_model_name = seriesName + gap + saleCode + gap + battery + gap + vehicle_model_code
  vehicle_model_id = str(vehicleModelIdStart+i-lineStart)
  # INSERT INTO `bss-gq`.`tab_vehicle_model` (`vehicle_model_id`, `vehicle_series_id`, `vehicle_model_code`, `vehicle_model_name`, `vehicle_model_sales_name`, `year`, `type`, `model_config`, `tsp_type`, `img`, `img1`) VALUES ('123', '8', 'G5C92DA3CN12', '魅MAX630高配10月版', '魅MAX630高配10月版', '2020', '1', '1', 'TSP2.0', 'https://iov-app-carmodel-1254959430.cos.ap-guangzhou.myqcloud.com/carmodel.png', 'https://iov-app-carmodel-1254959430.cos.ap-guangzhou.myqcloud.com/index_carmodel.png');
  sql = 'INSERT INTO `bss-gq`.`tab_vehicle_model` (`vehicle_model_id`, `vehicle_series_id`, `vehicle_model_code`, `vehicle_model_name`, `vehicle_model_sales_name`, `year`, `type`, `model_config`, `tsp_type`, `img`, `img1`) VALUES ('
  sql = sql + '\'' + '{:0>5}'.format(vehicle_model_id) + '\', '
  sql = sql + '\'' + vehicle_series_id + '\', '
  sql = sql + '\'' + vehicle_model_code + '\', '
  sql = sql + '\'' + vehicle_model_name + '\', '
  sql = sql + '\'' + vehicle_model_name + '\', '
  sql = sql + '\'' + year + '\', '
  sql = sql + '\'' + type + '\', '
  sql = sql + '\'' + model_config + '\', '
  sql = sql + '\'' + tsp_type + '\', '
  sql = sql + '\'' + img + '\', '
  sql = sql + '\'' + img1 + '\''
  #sql = sql + '\'' + img1 + '\', '
  #sql = sql + '\'' + img2 + '\', '
  sql = sql + ')'
  #printt(sql)
  return sql


printt('start: '+time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))
printt(sheets)
for sheet in sheets:
    if sheet != 'A18 V7配置表-20201221':
        continue

    sqls = ''
    ws = wb[sheet]
    for i in range(lineStart, ws.max_row + 1, offset):
        printt('line: '+str(i))
        try:
            sqls = sqls + fun(ws,i) + ';\r\n'
        except Exception as e:
            # 打印错误信息
            printt('Exception: '+str(i)+'\r\n\r\n')
            traceback.print_exc()
            continue
    printt('sqls: '+sqls)

printt('end: '+time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))